/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sqlgenerators.search;

import entities.Search;
import entities.SearchEducationalInfoRequerimentAndOperator;

/**
 *
 * @author damfercer
 */
public class EducationalAndResitrictionsSQLGenerator implements SearchSqlGeneratorInterface{
    private String res;
    @Override
    public String getSqlFromSearch(Search search) {
        res="";
        if(!search.getSearchEducationalInfoRequerimentAndOperatorList().isEmpty()){
            res+=" AND (";
            int i=0;
            for(SearchEducationalInfoRequerimentAndOperator restriction: search.getSearchEducationalInfoRequerimentAndOperatorList()){
                if(i==0){
                    res+="TRUE";
                    res+=generateSearchEducationalInfoRequerimentAndOperatorSQL(restriction);
                }
                else{
                    res+=generateSearchEducationalInfoRequerimentAndOperatorSQL(restriction);
                }
                i++;
            }
            res+=")";
        }
        return res;
    }
    
    private String generateSearchEducationalInfoRequerimentAndOperatorSQL(SearchEducationalInfoRequerimentAndOperator restr){
        String result="";
        if(restr.getSector()!=null || restr.getMinimumEducationalLevel()!=null){
            result=" AND EXISTS(SELECT ID_CANDIDATE_EDUCATIONAL_INFO FROM candidate_educational_info WHERE candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE AND candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL";
            if(restr.getSector()!=null){
                result+=" AND candidate_educational_info.SECTOR="+restr.getSector().getIdSector();
            }
            if(restr.getMinimumEducationalLevel()!=null){
                result+=" AND educational_level.EDUCATIONAL_VALUE>="+restr.getMinimumEducationalLevel().getEducationalValue();
            }
            result+=")";
        }
        return result;
    }
}
